﻿using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using TomNet.AspNetCore.Mvc;
using TomNet.Demo.Entity.Entities;

namespace TomNet.Demo.Web.Controllers
{
    public class OrmController : ApiControllerBase
    {
        private readonly IFreeSql _fsql;
        public OrmController(IFreeSql fsql)
        {
            _fsql = fsql;
        }

        [HttpGet("initdata")]
        public async Task<IActionResult> InitDataAsync()
        {
            #region  一对一数据
            var repo1 = _fsql.GetRepository<User>();
            var u1 = await repo1.InsertAsync(new User { Id = 1, Name = "tommy1" });
            var u2 = await repo1.InsertAsync(new User { Id = 2, Name = "tommy2" });

            var repo2 = _fsql.GetRepository<UserExt>();
            var ui1 = await repo2.InsertAsync(new UserExt { Id = 1, NickName = "tom1" });
            var ui2 = await repo2.InsertAsync(new UserExt { Id = 2, NickName = "tom2" });
            #endregion

            #region 一对多数据
            var repo3 = _fsql.GetRepository<DepartmentEntity>();
            var d1 = repo3.Insert(new DepartmentEntity { Name = "部门1" });
            var d2 = repo3.Insert(new DepartmentEntity { Name = "部门2" });

            var repo4 = _fsql.GetRepository<EmployeeEntity>();
            var e1 = repo4.Insert(new EmployeeEntity { DepartmentId = d1.Id, Name = "员工1" });
            var e2 = repo4.Insert(new EmployeeEntity { DepartmentId = d1.Id, Name = "员工2" });
            var e3 = repo4.Insert(new EmployeeEntity { DepartmentId = d2.Id, Name = "员工3" });
            var e4 = repo4.Insert(new EmployeeEntity { DepartmentId = d2.Id, Name = "员工4" });
            #endregion


            #region  多对多数据
            var repo5 = _fsql.GetRepository<SongEntity>();
            var s1 = repo5.Insert(new SongEntity { Name = "歌曲1" });
            var s2 = repo5.Insert(new SongEntity { Name = "歌曲2" });

            var repo6 = _fsql.GetRepository<TagEntity>();
            var t1 = repo6.Insert(new TagEntity { Name = "标签1" });
            var t2 = repo6.Insert(new TagEntity { Name = "标签2" });
            var t3 = repo6.Insert(new TagEntity { Name = "标签3" });
            var t4 = repo6.Insert(new TagEntity { Name = "标签4" });

            var repo7 = _fsql.GetRepository<SongTagEntity>();
            var st1 = repo7.Insert(new SongTagEntity { SongId = s1.Id, TagId = t1.Id });
            var st2 = repo7.Insert(new SongTagEntity { SongId = s1.Id, TagId = t2.Id });
            var st3 = repo7.Insert(new SongTagEntity { SongId = s1.Id, TagId = t3.Id });

            var st4 = repo7.Insert(new SongTagEntity { SongId = s2.Id, TagId = t2.Id });
            var st5 = repo7.Insert(new SongTagEntity { SongId = s2.Id, TagId = t3.Id });
            var st6 = repo7.Insert(new SongTagEntity { SongId = s2.Id, TagId = t4.Id });
            #endregion

            #region 添加数据
            var repo = _fsql.GetRepository<AreaEntity>();
            var a1 = new AreaEntity { Name = "中国" };
            var i1 = repo.Insert(a1);

            var a2 = new AreaEntity { ParentId = i1.Id, Name = "吉林省" };
            var a3 = new AreaEntity { ParentId = i1.Id, Name = "辽宁省" };
            var i2 = repo.Insert(a2);
            var i3 = repo.Insert(a3);

            var a4 = new AreaEntity { ParentId = i2.Id, Name = "长春市" };
            var a5 = new AreaEntity { ParentId = i2.Id, Name = "四平市" };
            var i4 = repo.Insert(a4);
            var i5 = repo.Insert(a5);

            var a6 = new AreaEntity { ParentId = i3.Id, Name = "沈阳市" };
            var a7 = new AreaEntity { ParentId = i3.Id, Name = "鞍山市" };
            var i6 = repo.Insert(a6);
            var i7 = repo.Insert(a7);


            var a8 = new AreaEntity { ParentId = i4.Id, Name = "南关区" };
            var a9 = new AreaEntity { ParentId = i4.Id, Name = "宽城区" };
            var i8 = repo.Insert(a8);
            var i9 = repo.Insert(a9);

            var a10 = new AreaEntity { ParentId = i6.Id, Name = "和平区" };
            var a11 = new AreaEntity { ParentId = i6.Id, Name = "沈河区" };
            var i10 = repo.Insert(a10);
            var i11 = repo.Insert(a11);
            #endregion
            return Ok();
        }


        /// <summary>
        /// 一对一关系
        /// </summary>
        [HttpGet("onetoone")]
        public IActionResult OneToOne()
        {
            #region  查询
            var q1 = _fsql.Select<User>().Include(m => m.UserExt).Where(m => m.Id == 1).ToList();
            var q2 = _fsql.Select<UserExt>().Include(m => m.User).Where(m => m.User.Id == 1).ToList();
            #endregion

            return Ok(new { q1, q2 });
        }

        /// <summary>
        /// 一对多 多对一
        /// </summary>
        [HttpGet("onetomany")]
        public IActionResult OneToMany()
        {
            #region 查询
            //一对多查询
            var q1 = _fsql.Select<DepartmentEntity>()
                        .IncludeMany(m => m.Employees)
                        .Where(m => m.Id == 1)
                        .ToList();
            // 会查询2次 但性能会比较好
            // SELECT a.`Id`, a.`Name`  FROM `Department` a WHERE(a.`Id` = 1 )
            // SELECT a.`Id`, a.`Name`, a.`DepartmentId`  FROM `Employee` a WHERE(((a.`DepartmentId` ) IN(1) ) )

            // 多对一查询
            var q2 = _fsql.Select<EmployeeEntity>()
                    .Include(m => m.Department)
                    .Where(m => m.DepartmentId == 1)
                    .ToList();

            var q3 = _fsql.Select<EmployeeEntity>()
                    .Where(m => m.Department.Id == 1)
                    .ToList();

            //SELECT
            //    a.`Id`,
            //    a.`Name`,
            //    a.`DepartmentId`,
            //    a__Department.`Id` as4,
            //    a__Department.`Name` as5
            //FROM
            //    `Employee` a
            //    LEFT JOIN `Department` a__Department ON a__Department.`Id` = a.`DepartmentId` 
            //WHERE
            //    (a__Department.`Id` = 1)

            // 这2个查询的结果相同，生成的 SQL 基本一样
            //==============================================================
            #endregion

            return Ok(new { q1, q2, q3 });
        }

        /// <summary>
        /// 多对多
        /// </summary>
        [HttpGet("manytomany")]
        public IActionResult ManyToMany()
        {
            #region  查询
            var q1 = _fsql.Select<SongEntity>()
                       .IncludeMany(a => a.Tags)
                       .Where(a => new[] { "歌曲1" }.Contains(a.Name))
                       .ToList();

            var q2 = _fsql.Select<TagEntity>()
                       .IncludeMany(a => a.Songs)
                       .Where(a => new[] { "标签2" }.Contains(a.Name))
                       .ToList();
            #endregion
            return Ok(new { q1, q2 });
        }

        /// <summary>
        /// 非常多关系
        /// </summary>
        [HttpGet("toomany")]
        public async Task<IActionResult> TooManyAsync()
        {
            var repo = _fsql.GetRepository<TableA>();
            // A关联B、C
            // C关联D
            // D关联E

            var query1 = await repo.Select
                  .IncludeMany(m => m.TableB)
                  .IncludeMany(m => m.TableC, x => x.IncludeMany(m => m.TableD, x => x.IncludeMany(m => m.TableE)))
                  .ToListAsync(a => new { a.Name, a.TableB, a.TableC });

            var query2 = await repo.Select
                  .IncludeMany(m => m.TableB, x => x.Where(y => y.Name == "111"))
                  .IncludeMany(m => m.TableC, x => x.Where(y => y.Name == "222")
                  .IncludeMany(m => m.TableD, x => x.Where(y => y.Name == "333")
                  .IncludeMany(m => m.TableE, x => x.Where(y => y.Name == "444"))))
                  .ToListAsync(a => new { a.Name, a.TableB, a.TableC });

            var query3 = await repo.Select
                  .IncludeMany(m => m.TableB.Select(a => new TableB { Id = a.Id, Name = a.Name }), x => x.Where(y => y.Name == "111"))
                  .IncludeMany(m => m.TableC.Select(a => new TableC { Id = a.Id, Name = a.Name }), x => x.Where(y => y.Name == "222")
                  .IncludeMany(m => m.TableD.Select(a => new TableD { Id = a.Id, Name = a.Name }), x => x.Where(y => y.Name == "333")
                  .IncludeMany(m => m.TableE.Select(a => new TableE { Id = a.Id, Name = a.Name }), x => x.Where(y => y.Name == "444"))))
                  .ToListAsync(a => new { a.Name, a.TableB, a.TableC });

            return Ok();
        }

        /// <summary>
        /// 父子关系
        /// </summary>
        [HttpGet("tree")]
        public async Task<IActionResult> TreeAsync()
        {
            var repo = _fsql.GetRepository<AreaEntity>();
            // 从根节点开始查，查询全部节点信息
            var list1 = await repo.Select.ToTreeListAsync();
            // 查询节点下的所有子节点
            var list2 = await repo.Select.Where(m => m.Name == "辽宁省").AsTreeCte().ToTreeListAsync();
            // 查询节点
            var list3 = await repo.Select.Where(m => m.Parent.Name == "辽宁省").ToTreeListAsync();
            var list4 = await repo.Select.Where(m => m.Parent.Parent.Name == "辽宁省").ToTreeListAsync();
            return Ok(new { list1, list2, list3, list4 });
        }
    }
}
